package cn.com.nes.site.module.dao;

import cn.com.nes.common.util.DBUtils;
import cn.com.nes.common.util.Globals;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.DataRes;
import cn.com.nes.site.entity.PageData;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.*;

public abstract class BaseDaoImpl {

    private JdbcTemplate jdbcTemplate_newg;

    private JdbcTemplate jdbcTemplate_mall;

    private JdbcTemplate jdbcTemplate_ctce;

    @Autowired
    private DataSourceTransactionManager transactionManager_newg;

    @Autowired
    private DataSourceTransactionManager transactionManager_mall;

    @Autowired
    private DataSourceTransactionManager transactionManager_ctce;

    public JdbcTemplate getJdbcTemplate_newg() {
        return jdbcTemplate_newg;
    }

    @Resource
    public void setJdbcTemplate_newg(JdbcTemplate jdbcTemplate_newg) {
        this.jdbcTemplate_newg = jdbcTemplate_newg;
    }

    public void createTable_newg(String sql) {
        this.jdbcTemplate_newg.execute(sql);
    }

    public Connection getDBConnect_newg() throws SQLException {
        return jdbcTemplate_newg.getDataSource().getConnection();
    }

    public JdbcTemplate getJdbcTemplate_mall() {
        return jdbcTemplate_mall;
    }

    @Resource
    public void setJdbcTemplate_mall(JdbcTemplate jdbcTemplate_mall) {
        this.jdbcTemplate_mall = jdbcTemplate_mall;
    }

    public void createTable_mall(String sql) {
        this.jdbcTemplate_mall.execute(sql);
    }

    public Connection getDBConnect_mall() throws SQLException {
        return jdbcTemplate_mall.getDataSource().getConnection();
    }

    public JdbcTemplate getJdbcTemplate_ctce() {
        return jdbcTemplate_ctce;
    }

    @Resource
    public void setJdbcTemplate_ctce(JdbcTemplate jdbcTemplate_ctce) {
        this.jdbcTemplate_ctce = jdbcTemplate_ctce;
    }

    public void createTable_ctce(String sql) {
        this.jdbcTemplate_ctce.execute(sql);
    }

    public Connection getDBConnect_ctce() throws SQLException {
        return jdbcTemplate_ctce.getDataSource().getConnection();
    }

    /**
     * 开启newg事务
     */
    public TransactionStatus begin_newg() {
        DefaultTransactionDefinition def = new DefaultTransactionDefinition();//事务定义类
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        TransactionStatus status = transactionManager_newg.getTransaction(def);// 返回事务对象
        return status;
    }

    /**
     * 提交newg事务
     *
     * @param status
     */
    public void commit_newg(TransactionStatus status) {
        if (!status.isCompleted()) {
            transactionManager_newg.commit(status);
        }
    }

    /**
     * newg事务回滚
     *
     * @param status
     */
    public void rollback_newg(TransactionStatus status) {
        if (!status.isCompleted()) {
            try {
                transactionManager_newg.rollback(status);
            } catch (Exception e) {
            }
        }
    }

    /**
     * 开启shop事务
     */
    public TransactionStatus begin_mall() {
        DefaultTransactionDefinition def = new DefaultTransactionDefinition();//事务定义类
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        TransactionStatus status = transactionManager_mall.getTransaction(def);// 返回事务对象
        return status;
    }

    /**
     * 提交shop事务
     *
     * @param status
     */
    public void commit_mall(TransactionStatus status) {
        if (!status.isCompleted()) {
            transactionManager_mall.commit(status);
        }
    }

    /**
     * newg事务回滚
     *
     * @param status
     */
    public void rollback_mall(TransactionStatus status) {
        if (!status.isCompleted()) {
            try {
                transactionManager_mall.rollback(status);
            } catch (Exception e) {
            }
        }
    }

    /**
     * 开启ctce事务
     */
    public TransactionStatus begin_ctce() {
        DefaultTransactionDefinition def = new DefaultTransactionDefinition();//事务定义类
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        TransactionStatus status = transactionManager_ctce.getTransaction(def);// 返回事务对象
        return status;
    }

    /**
     * 提交ctce事务
     *
     * @param status
     */
    public void commit_ctce(TransactionStatus status) {
        transactionManager_ctce.commit(status);
    }

    /**
     * ctce事务回滚
     *
     * @param status
     */
    public void rollback_ctce(TransactionStatus status) {
        transactionManager_ctce.rollback(status);
    }

    public PageData insertTableData(TransactionStatus newg_status, DataReq req) {
        PageData res = new PageData();

        PageData sqlInfo = getInsertSQL(req);
        if (sqlInfo.getRetCode() != 0) {
            rollback_newg(newg_status);
            return sqlInfo;
        }
        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.getJdbcTemplate_newg().update(sb_field.toString(), listData.toArray());

        //线索添加id把uuid转换成clueid
        String uuid = (String) req.getReqData().get("uuid");
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateClueId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("clueId"))) {
                req.getReqData().put("clueId", uuid);
            } else {
                req.getReqData().put("clueId", req.getReqData().get("clueId"));
            }

        }
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateCustomerId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("customerId"))) {
                req.getReqData().put("customerId", uuid);
            } else {
                req.getReqData().put("customerId", req.getReqData().get("customerId"));
            }

        }
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateOpportunityId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("opportunityId"))) {
                req.getReqData().put("opportunityId", uuid);
            } else {
                req.getReqData().put("opportunityId", req.getReqData().get("opportunityId"));
            }

        }
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateProjectId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("projectId"))) {
                req.getReqData().put("projectId", uuid);
            } else {
                req.getReqData().put("projectId", req.getReqData().get("projectId"));
            }

        }

        // 添加操作日志
        if (!"0".equals(req.getReqData().get("isLog"))) {
            //添加操作日志
            SaveOperatelog(req, (String) req.getReqData().get("OperationLogType"), (String) req.getReqData().get("OperationLogName"), sb_field + "---" + listData, newg_status);
        }

        req.getReqData().put("uuid", uuid);
        res.setRetCode(0);
        res.setRetMessage("OK");
        res.setKey(uuid);
        return res;
    }

    public PageData getDetailsByKey(Map<String, Object> param) {
        PageData res = new PageData();
        PageData sqlInfo = getbaseQuerySQL(param);
        if (sqlInfo.getRetCode() != 0) {
            return sqlInfo;
        }

        res.setRetCode(9);
        res.setRetMessage("没有查询到详细数据");

        StringBuffer sb = (StringBuffer) sqlInfo.getData().get("sb");
        List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        List<Map<String, Object>> list = this.getJdbcTemplate_newg()
                .queryForList(sb.toString(), listData.toArray());
        if (list.size() == 1) {
            res.setRetCode(0);
            res.setData(list.get(0));
            res.setRetMessage("OK");
        }
        return res;
    }

    public PageData updateTableData(TransactionStatus newg_status, DataReq req) {
        PageData res = new PageData();
        PageData sqlInfo = getUpdateSQL(req);
        if (sqlInfo.getRetCode() != 0) {
            rollback_newg(newg_status);
            return sqlInfo;
        }
        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.getJdbcTemplate_newg().update(sb_field.toString(),
                listData.toArray());

        //线索添加id把uuid转换成clueid
        String uuid = (String) req.getReqData().get("uuid");
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateClueId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("clueId"))) {
                req.getReqData().put("clueId", uuid);
            } else {
                req.getReqData().put("clueId", req.getReqData().get("clueId"));
            }
        }
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateCustomerId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("customerId"))) {
                req.getReqData().put("customerId", uuid);
            } else {
                req.getReqData().put("customerId", req.getReqData().get("customerId"));
            }
        }
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateOpportunityId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("opportunityId"))) {
                req.getReqData().put("opportunityId", uuid);
            } else {
                req.getReqData().put("opportunityId", req.getReqData().get("opportunityId"));
            }
        }
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateProjectId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("projectId"))) {
                req.getReqData().put("projectId", uuid);
            } else {
                req.getReqData().put("projectId", req.getReqData().get("projectId"));
            }
        }

        SaveOperatelog(req, (String) req.getReqData().get("OperationLogType"), (String) req.getReqData().get("OperationLogName"), sb_field + "---" + listData, newg_status);

        req.getReqData().put("uuid", uuid);
        res.setRetCode(0);
        res.setRetMessage("OK");
        res.setKey(uuid);
        return res;
    }

    public PageData deleteTableData(TransactionStatus newg_status, DataReq req) {
        PageData res = new PageData();
        PageData sqlInfo = deleteSQL(req);
        if (sqlInfo.getRetCode() != 0) {
            rollback_newg(newg_status);
            return sqlInfo;
        }
        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.getJdbcTemplate_newg().update(sb_field.toString(),
                listData.toArray());

        SaveOperatelog(req, (String) req.getReqData().get("OperationLogType"), (String) req.getReqData().get("OperationLogName"), sb_field + "---" + listData, newg_status);

        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }


    public PageData getInsertSQL(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        UUID uuid = UUID.randomUUID();
        String tableName = (String) param.get("tableName");
        if (StringUtils.isEmpty((String) param.get("uuid"))) {
            param.put("uuid", uuid.toString());
        }

        StringBuffer sb_field = new StringBuffer();
        StringBuffer sb = new StringBuffer();
        StringBuffer sb_value = new StringBuffer();

        if (req.getUinfo() != null) {
            param.put("inputUser", req.getUinfo().getId());
            param.put("createUid", req.getUinfo().getId());
            param.put("modifyUid", req.getUinfo().getId());
        } else {
            param.put("createUid", 000);
            param.put("modifyUid", 000);
        }
        String t = DBUtils.getCurTime();
        param.put("createTime", t);
        param.put("modifyTime", t);

        List<String> listData = new ArrayList<String>();
        sb.delete(0, sb.length());
        sb.append(" select column_name column_name,is_nullable,column_type,column_comment,column_default,data_type  from INFORMATION_SCHEMA.Columns where lower(TABLE_SCHEMA)='").append(Globals.DBNAME).append("' and lower(table_name)=? and extra<>'auto_increment' ");
        List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{tableName});
        for (Map<String, Object> data : list) {
            String column_name = data.get("column_name") == null ? (String) data.get("column_name".toUpperCase()) : (String) data.get("column_name");
            String is_nullable = data.get("is_nullable") == null ? (String) data.get("is_nullable".toUpperCase()) : (String) data.get("is_nullable");
            String column_comment = data.get("column_comment") == null ? (String) data.get("column_comment".toUpperCase()) : (String) data.get("column_comment");
            String column_default = data.get("column_default") == null ? (String) data.get("column_default".toUpperCase()) : (String) data.get("column_default");
            String column_value = Objects.toString(param.get(column_name), null);
            String data_type = data.get("data_type") == null ? (String) data.get("data_type".toUpperCase()) : (String) data.get("data_type");

            if ("NO".equals(is_nullable)) {
                if (StringUtils.isBlank(column_value) && StringUtils.isBlank(column_default)) {
                    res.setRetCode(9);
                    res.setRetMessage(column_comment + "(" + column_name + ")不能为空！");
                    return res;
                }
            }

            if (column_value != null) {
                if (data_type.indexOf("int") >= 0 || data_type.indexOf("decimal") >= 0) {
                    if (!DBUtils.isNumber(column_value)) {
                        res.setRetCode(9);
                        res.setRetMessage(column_comment + "(" + column_name + ")类型错误！");
                        return res;
                    }
                }
            }

            if (!StringUtils.isBlank(column_value)) {
                sb_field.append("`").append(column_name).append("`,");
                sb_value.append("?").append(",");
                listData.add(column_value);
            }
        }
        sb_field.deleteCharAt(sb_field.length() - 1);
        sb_value.deleteCharAt(sb_value.length() - 1);
        sb_field.insert(0, "(").insert(0, "`" + tableName + "`").insert(0, "insert into ");
        sb_field.append(")values(").append(sb_value).append(")");
        Map<String, Object> data = new HashMap<String, Object>();
        data.put("sb_field", sb_field);
        data.put("listData", listData);
        res.setData(data);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getUpdateSQL(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        String tableName = (String) param.get("tableName");
        StringBuffer sb_field = new StringBuffer();
        StringBuffer sb = new StringBuffer();
        StringBuffer sb_where = new StringBuffer();
        List<String> listData = new ArrayList<String>();
        String keys = (String) param.get("keys");
        if (keys == null || keys.equals("")) {
            res.setRetCode(9);
            res.setRetMessage("主键不能为空！");
            return res;
        }
        String[] keylist = keys.split(";");
        for (int i = 0; i < keylist.length; i++) {
            String val = (String) param.get(keylist[i]);
            if (StringUtils.isEmpty(val)) {
                res.setRetCode(9);
                res.setRetMessage(keylist[i] + "不能为空！");
                return res;
            }
        }
        if (req.getUinfo() != null) {
            param.put("modifyUid", req.getUinfo().getId());
        } else {
            param.put("modifyUid", 000);
        }

        String t = DBUtils.getCurTime();
        param.put("modifyTime", t);

        sb_where.append(" where ");


        sb.delete(0, sb.length());
        sb.append(" select column_name column_name,is_nullable,column_type,column_comment,column_default,data_type  from INFORMATION_SCHEMA.Columns where lower(TABLE_SCHEMA)='").append(Globals.DBNAME).append("' and lower(table_name)=? and extra<>'auto_increment' ");
        List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{tableName});
        for (Map<String, Object> data : list) {
            String column_name = data.get("column_name") == null ? (String) data.get("column_name".toUpperCase()) : (String) data.get("column_name");
            String is_nullable = data.get("is_nullable") == null ? (String) data.get("is_nullable".toUpperCase()) : (String) data.get("is_nullable");
            String column_comment = data.get("column_comment") == null ? (String) data.get("column_comment".toUpperCase()) : (String) data.get("column_comment");
            String column_default = data.get("column_default") == null ? (String) data.get("column_default".toUpperCase()) : (String) data.get("column_default");
            String column_value = Objects.toString(param.get(column_name), null);
            String data_type = data.get("data_type") == null ? (String) data.get("data_type".toUpperCase()) : (String) data.get("data_type");

            if ("NO".equals(is_nullable)) {
                if ("".equals(column_value) && StringUtils.isBlank(column_default)) {
                    res.setRetCode(9);
                    res.setRetMessage(column_comment + "不能为空！");
                    return res;
                }
            }

            if (column_value != null) {
                if (data_type.indexOf("int") >= 0 || data_type.indexOf("decimal") >= 0) {
                    if (!DBUtils.isNumber(column_value)) {
                        res.setRetCode(9);
                        res.setRetMessage(column_comment + "(" + column_name + ")类型错误！");
                        return res;
                    }
                }
            }

            if (column_value != null) {
                boolean isPK = false;
                for (String key_name : keylist) {
                    if (column_name.equals(key_name)) {
                        isPK = true;
                        break;
                    }
                }
                if (!isPK) {
                    sb_field.append("`").append(column_name).append("`=?,");
                    listData.add(column_value);
                }
            }
        }
        if(sb_field.length()!=0) {
            sb_field.deleteCharAt(sb_field.length() - 1);
        }
        sb_field.insert(0, " set ").insert(0, tableName).insert(0, "update ");


        for (String column_name : keylist) {
            sb_where.append(column_name).append("=? and ");
            String column_value = (String) param.get(column_name);
            listData.add(column_value);
        }
        sb_where.append(" 1=1 ");
        sb_field.append(sb_where);

        Map<String, Object> data = new HashMap<String, Object>();
        data.put("sb_field", sb_field);
        data.put("listData", listData);
        res.setData(data);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData deleteSQL(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        String tableName = (String) param.get("tableName");
        StringBuffer sb_field = new StringBuffer();
        StringBuffer sb = new StringBuffer();
        StringBuffer sb_where = new StringBuffer();
        List<String> listData = new ArrayList<String>();
        String keys = (String) param.get("keys");
        if (keys == null || keys.equals("")) {
            res.setRetCode(9);
            res.setRetMessage("主键不能为空！");
            return res;
        }
        String[] keylist = keys.split(";");
        for (int i = 0; i < keylist.length; i++) {
            String val = (String) param.get(keylist[i]);
            if (StringUtils.isEmpty(val)) {
                res.setRetCode(9);
                res.setRetMessage(keylist[i] + "不能为空！");
                return res;
            }
        }


        sb_field.append(" delete from ").append(tableName).append(" where ");


        for (String column_name : keylist) {
            sb_where.append(column_name).append("=? and ");
            String column_value = (String) param.get(column_name);
            listData.add(column_value);
        }
        sb_where.append(" 1=1 ");
        sb_field.append(sb_where);

        Map<String, Object> data = new HashMap<String, Object>();
        data.put("sb_field", sb_field);
        data.put("listData", listData);
        res.setData(data);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public List<Map<String, Object>> getbaseQueryData(Map<String, Object> param) {
        PageData res = new PageData();
        PageData sqlInfo = getbaseQuerySQL(param);
        StringBuffer sb = (StringBuffer) sqlInfo.getData().get("sb");
        List<String> listData = (List<String>) sqlInfo.getData().get(
                "listData");
        List<Map<String, Object>> list = this.getJdbcTemplate_newg()
                .queryForList(sb.toString(), listData.toArray());
        return list;
    }

    public PageData getbaseQuerySQL(Map<String, Object> param) {
        PageData res = new PageData();
        String tableName = (String) param.get("tableName");
        StringBuffer sb = new StringBuffer();
        StringBuffer sb_where = new StringBuffer();
        List<String> listData = new ArrayList<String>();
        String keys = (String) param.get("keys");
        if (keys == null || keys.equals("")) {
            res.setRetCode(9);
            res.setRetMessage("主键不能为空！");
            return res;
        }
        sb_where.append(" from ").append(tableName).append(" a where ");
        String[] keylist = keys.split(";");
        sb.delete(0, sb.length());
        sb.append(" select lower(column_name) column_name,column_comment,CONVERT(data_type , char) data_type  from INFORMATION_SCHEMA.Columns where lower(TABLE_SCHEMA)='").append(Globals.DBNAME).append("' and lower(table_name)=? ");
        List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{tableName});

        sb.delete(0, sb.length());
        sb.append("select ");
        for (Map<String, Object> data : list) {
            String column_name = (String) data.get("column_name");
            String data_type = (String) data.get("data_type");
            if ("timestamp".equals(data_type)) {
                sb.append("date_format(a.").append(column_name).append(",'%Y-%m-%d %T') ").append(column_name).append(",");
            } else {
                sb.append("a.").append(column_name).append(",");
            }
        }
        sb.deleteCharAt(sb.length() - 1);
        for (String column_name : keylist) {
            sb_where.append(column_name).append("=?,");
            String column_value = (String) param.get(column_name);
            listData.add(column_value);
        }
        sb_where.deleteCharAt(sb_where.length() - 1);
        sb.append(sb_where);
        Map<String, Object> data = new HashMap<String, Object>();
        data.put("sb", sb);
        data.put("listData", listData);
        res.setData(data);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData isExists(DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        String tableName = (String) param.get("tableName");
        StringBuffer sb = new StringBuffer();
        StringBuffer sb_where = new StringBuffer();
        String keys = (String) param.get("keys");
        if (keys == null || keys.equals("")) {
            res.setRetCode(9);
            res.setRetMessage("主键不能为空！");
            return res;
        }
        sb.delete(0, sb.length());
        sb.append(" select count(1) cnt from  ").append(tableName);
        sb_where.append(" where ");
        String[] keylist = keys.split(";");
        for (String key_name : keylist) {
            sb_where.append(key_name).append("='").append(param.get(key_name)).append("' and ");
        }
        sb_where.append(" 1=1 ");
        sb.append(sb_where.toString());
        sb = DBUtils.sql_check(sb);
        int cnt = this.getJdbcTemplate_newg().queryForObject(sb.toString(), new Object[]{}, Integer.class);
        if (cnt == 0) {
            res.setRetCode(0);
            res.setRetMessage("OK");
        } else {
            res.setRetCode(9);
            res.setRetMessage(keys + "已经存在！");
        }
        return res;
    }

    @SuppressWarnings("unchecked")
    public PageData SaveSyslog(DataReq req, String yhmk, String remark, String sqllog, TransactionStatus newg_status) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("系统日志添加失败");
        param.put("tableName", "t_logs");
        param.put("id", req.getUinfo().getId());
        param.put("yhbs", req.getUinfo().getUsername());
        param.put("yhmk", yhmk);
        param.put("remark", remark);
        param.put("sqllog", sqllog);
        try {
            PageData sqlInfo = getInsertSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            StringBuffer log_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> log_listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(log_field.toString(), log_listData.toArray());
            res.setKey((String) param.get("uuid"));
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {

        }
        return res;
    }

    @RequestMapping(method = RequestMethod.POST, value = "/getUUID")
    public @ResponseBody
    PageData getUUID(HttpServletRequest request,
                     HttpServletResponse response, ModelMap modelMap) throws Exception {
        DataRes req_Status = DBUtils.setRemoteVisit(request, response, true);
        PageData res = new PageData();
        if (req_Status.getRetCode() != 0) {
            res = new PageData();
            res.setRetCode(req_Status.getRetCode());
            res.setRetMessage(req_Status.getRetMessage());
            return res;
        }

        UUID uuid = UUID.randomUUID();
        res.setKey(uuid.toString());

        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    //根据父ID查询部门下所有子部门
    public String getAllNextCompanyId(String p_companyId) {
        String companyId = "";
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT companyId FROM ( ");
        sb.append(" SELECT ");
        sb.append(" @ids AS _ids, ");
        sb.append(" ( SELECT @ids := GROUP_CONCAT( companyId ) FROM t_company WHERE FIND_IN_SET( p_companyId, @ids ) ) AS cids, ");
        sb.append(" @l := @l + 1 AS LEVEL ");
        sb.append(" FROM ");
        sb.append(" t_company,( SELECT @ids := ?, @l := 0 ) b ");
        sb.append(" WHERE ");
        sb.append(" @ids IS NOT NULL ");
        sb.append(" ) id, ");
        sb.append(" t_company DATA ");
        sb.append(" WHERE ");
        sb.append(" FIND_IN_SET( DATA.companyId, ID._ids ) ");
        sb.append(" ORDER BY ");
        sb.append(" LEVEL,companyId ");
        List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{p_companyId});
        if (list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                if (i == 0) {
                    companyId = "'" + list.get(i).get("companyId").toString() + "'";
                } else {
                    companyId = companyId + "," + "'" + list.get(i).get("companyId").toString() + "'";
                }
            }
        }
        return companyId;
    }

    //根据已有公司生成下一个子公司ID
    public String getNextId(String p_companyId) {
        String aaaa = "";
        String companyId = "";
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT RIGHT(ifnull(max(companyId),'0000'),4) companyId from t_company where p_companyId=? and length(companyId) = length(p_companyId) + 4 ");
        List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{p_companyId});
        if (list.size() > 0) {
            int nextCompanyId = Integer.parseInt(list.get(0).get("companyId").toString()) + 1;
            if (nextCompanyId > 999) {
                companyId = String.valueOf(nextCompanyId);
            } else if (nextCompanyId > 99 && nextCompanyId <= 999) {
                companyId = String.valueOf("0" + nextCompanyId);
            } else if (nextCompanyId > 9 && nextCompanyId <= 99) {
                companyId = String.valueOf("00" + nextCompanyId);
            } else if (nextCompanyId <= 9) {
                companyId = String.valueOf("000" + nextCompanyId);
            }
        } else {
            companyId = "0000";
        }

        if (companyId != null && !"".equals(companyId)) {
            aaaa = p_companyId + companyId;
        }
        return aaaa;
    }

    //根据已有子部门ID生成下一个ID
    public String getNextDepartmentId(String p_companyId) {
        String aaaa = "";
        String DepartmentId = "";
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT RIGHT(ifnull(max(departmentId),'000'),3) departmentId from t_department where p_companyId=? and length(departmentId) = length(p_companyId) + 4  ");
        List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{p_companyId});
        if (list.size() > 0) {
            int nextDepartmentId = Integer.parseInt(list.get(0).get("departmentId").toString()) + 1;
//			if(nextDepartmentId>999){
//				DepartmentId="";
//			}else if(nextDepartmentId>99&&nextDepartmentId<=999){
//				DepartmentId=String.valueOf(nextDepartmentId);
//			}else if(nextDepartmentId>9&&nextDepartmentId<=99){
//				DepartmentId=String.valueOf("0"+nextDepartmentId);
//			}else if(nextDepartmentId<=9){
//				DepartmentId=String.valueOf("00"+nextDepartmentId);
//			}
            if (nextDepartmentId > 999) {
                DepartmentId = "";
            } else {
                DepartmentId = String.format("%03d", nextDepartmentId);
            }
        } else {
            DepartmentId = "D001";
        }
        if (DepartmentId != null && !"".equals(DepartmentId)) {
            aaaa = p_companyId + "D" + DepartmentId;
        }
        return aaaa;
    }

    //根据行政区划代码截取查询所用代码
    public String getQueryXzqh(String xzqhdm, String name) {
        String result = "";
        StringBuffer sb = new StringBuffer();
        StringBuffer sbadd = new StringBuffer();
        if (xzqhdm == null || xzqhdm.equals("")) {
            return "";
        }
        String[] xzqhArr = xzqhdm.split(";");
        for (int i = 0; i < xzqhArr.length; i++) {
            sb.delete(0, sb.length());
            sb.append(" select XZQHJB from T_XZQH where XZQHDM= ? ");
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{xzqhArr[i]});
            if (list.size() > 0) {
                int jb = Integer.parseInt(list.get(0).get("XZQHJB").toString());
                if (jb == 0)
                    result = "";
                else if (jb == 1)
                    result = xzqhArr[i].substring(0, 2);
                else if (jb == 2)
                    result = xzqhArr[i].substring(0, 4);
                else if (jb == 3)
                    result = xzqhArr[i].substring(0, 6);
                else if (jb == 4)
                    result = xzqhArr[i].substring(0, 9);
                else if (jb == 5)
                    result = xzqhArr[i];
                else if (jb == 6)
                    result = xzqhArr[i];
                else
                    result = xzqhArr[i];
            }
            sbadd.append(name).append(" like '").append(result).append("%' ");
            if (i < xzqhArr.length - 1) {
                sbadd.append(" or ");
            }
        }
        sbadd.insert(0, " and (").append(")");
        return sbadd.toString();
    }

    @SuppressWarnings("unchecked")
    public PageData SaveOperatelog(DataReq req, String type, String remark, String sqllog, TransactionStatus newg_status) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("操作日志添加失败");
        param.put("tableName", "log_operate");
        param.put("uuid", UUID.randomUUID().toString());
        param.put("type", type);
        param.put("userid", req.getUinfo().getId());
        param.put("username", req.getUinfo().getUsername());
        param.put("companyId", req.getUinfo().getCompanyId());
        param.put("remark", remark);
        param.put("memo", req.getReqData().get("OperationMemo"));
        param.put("sqllog", sqllog);
        try {
            PageData sqlInfo = getInsertSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            StringBuffer log_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> log_listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(log_field.toString(), log_listData.toArray());
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {

        }
        return res;
    }

    @SuppressWarnings("unchecked")
    public PageData SaveOperatelog(DataReq req, String type, String remark, String sqllog, Connection conn) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("操作日志添加失败");
        param.put("tableName", "log_operate");
        param.put("uuid", UUID.randomUUID().toString());
        param.put("type", type);
        param.put("userid", req.getUinfo().getId());
        param.put("username", req.getUinfo().getUsername());
        param.put("companyId", req.getUinfo().getCompanyId());
        param.put("remark", remark);
        param.put("memo", req.getReqData().get("OperationMemo"));
        param.put("sqllog", sqllog);
        PageData sqlInfo = getInsertSQL(req);
        if (sqlInfo.getRetCode() != 0) {
            conn.rollback();
            return sqlInfo;
        }
        StringBuffer log_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        List<String> log_listData = (List<String>) sqlInfo.getData().get("listData");
        this.update(conn, log_field.toString(), log_listData.toArray());
        return res;
    }

    @SuppressWarnings("unchecked")
    public PageData addClaimlog(DataReq req, String type, TransactionStatus newg_status) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("拜访信息新增失败");
        param.put("tableName", "log_claim");
        param.put("type", type);
        param.put("uuid", UUID.randomUUID().toString());
        param.put("userid", req.getUinfo().getId());
        param.put("username", req.getUinfo().getUsername());
        param.put("companyId", req.getUinfo().getCompanyId());
        String claimUUID = (String) param.get("uuid");
        if (param.get("visittime").equals("") || "" == param.get("visittime")) {
            param.put("visittime", null);
        }
        try {
            PageData sqlInfo = getInsertSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(), listData.toArray());
            SaveOperatelog(req, type, "新增拜访信息", sb_field + "---" + listData, newg_status);
            param.put("uuid", claimUUID);
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {

        }
        return res;
    }

    @SuppressWarnings("unchecked")
    public PageData updateClaimlog(DataReq req, String type, TransactionStatus newg_status) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("拜访信息修改失败");
        param.put("tableName", "log_claim");
        param.put("keys", "uuid");
        param.put("uuid", param.get("uuid"));
        param.put("type", type);
        param.put("userid", req.getUinfo().getId());
        param.put("username", req.getUinfo().getUsername());
        param.put("companyId", req.getUinfo().getCompanyId());
        if (param.get("bfsj").equals("") || "" == param.get("bfsj")) {
            param.put("bfsj", null);
        }
        try {
            PageData sqlInfo = getUpdateSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(), listData.toArray());
            SaveOperatelog(req, type, "修改拜访信息", sb_field + "---" + listData, newg_status);
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {

        }
        return res;
    }

    public PageData delClaimlog(DataReq req, String type, TransactionStatus newg_status) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("拜访信息删除失败");
        try {
            String sb = "update log_claim set isdelete='1' where uuid='" + param.get("uuid") + "' and  1=1 ";
            this.getJdbcTemplate_newg().update(sb);
            SaveOperatelog(req, type, "删除拜访信息", sb, newg_status);
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {

        }
        return res;
    }

    //根据日期类型获取查询条件
    public String dateTypeCondition(String dateType) {
        StringBuffer sbform = new StringBuffer();
        sbform.delete(0, sbform.length());
        switch (dateType) {
            case "01": //今天
                sbform.append(" and a.createTime between curdate() and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "02": //昨天
                sbform.append(" and a.createTime between SUBDATE(curdate(),INTERVAL 1 day) and curdate() ");
                break;
            case "03": //最近7天
                sbform.append(" and a.createTime between SUBDATE(curdate(),INTERVAL 1 week) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "04": //最近30天
                sbform.append(" and a.createTime between SUBDATE(curdate(),INTERVAL 1 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "05": //最近3个月
                sbform.append(" and a.createTime between SUBDATE(curdate(),INTERVAL 3 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "06": //最近1年
                sbform.append(" and a.createTime between SUBDATE(curdate(),INTERVAL 1 year) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            default: //今天
                sbform.append(" and a.createTime between curdate() and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
        }
        return sbform.toString();
    }

    public String dateTypeCondition_mod(String dateType) {
        StringBuffer sbform = new StringBuffer();
        sbform.delete(0, sbform.length());
        switch (dateType) {
            case "01": //今天
                sbform.append(" and a.modifyTime between curdate() and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "02": //昨天
                sbform.append(" and a.modifyTime between SUBDATE(curdate(),INTERVAL 1 day) and curdate() ");
                break;
            case "03": //最近7天
                sbform.append(" and a.modifyTime between SUBDATE(curdate(),INTERVAL 1 week) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "04": //最近30天
                sbform.append(" and a.modifyTime between SUBDATE(curdate(),INTERVAL 1 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "05": //最近3个月
                sbform.append(" and a.modifyTime between SUBDATE(curdate(),INTERVAL 3 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "06": //最近1年
                sbform.append(" and a.modifyTime between SUBDATE(curdate(),INTERVAL 1 year) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            default: //今天
                sbform.append(" and a.modifyTime between curdate() and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
        }
        return sbform.toString();
    }

    public String dateTypeCondition_log(String dateType) {
        StringBuffer sbform = new StringBuffer();
        sbform.delete(0, sbform.length());
        switch (dateType) {
            case "01": //今天
                sbform.append(" and logdate between curdate() and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "02": //昨天
                sbform.append(" and logdate between SUBDATE(curdate(),INTERVAL 1 day) and curdate() ");
                break;
            case "03": //最近7天
                sbform.append(" and logdate between SUBDATE(curdate(),INTERVAL 1 week) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "04": //最近30天
                sbform.append(" and logdate between SUBDATE(curdate(),INTERVAL 1 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "05": //最近3个月
                sbform.append(" and logdate between SUBDATE(curdate(),INTERVAL 3 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "06": //最近1年
                sbform.append(" and logdate between SUBDATE(curdate(),INTERVAL 1 year) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            default: //今天
                sbform.append(" and logdate between curdate() and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
        }
        return sbform.toString();
    }

    public int update(Connection conn, String sql, Object[] obj) throws SQLException {
        PreparedStatement ps = null;
        int cnt = 0;
        try {
            ps = conn.prepareStatement(sql,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            if (obj != null && obj.length > 0) {
                for (int i = 0; i < obj.length; i++) {
                    ps.setObject(i + 1, obj[i]);
                }
            }
            cnt = ps.executeUpdate();
        } finally {
            ps.close();
        }
        return cnt;
    }

    public int updateBatch(Connection conn, String sql, List<Object[]> arr) throws SQLException {
        PreparedStatement ps = null;
        int cnt = 0;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0 ; i < arr.size(); i++) {
                if (arr.get(i) != null && arr.get(i).length > 0) {
                    for (int j = 0; j < arr.get(i).length; j++) {
                        ps.setObject(j+1, arr.get(i)[j]);
                    }
                    ps.addBatch();

                    if ((i + 1) % 500 == 0 || i == arr.size() -1) {
                        ps.executeBatch();

                        ps.clearBatch();
                    }
                }
            }
        } finally {
            ps.close();
        }
        return cnt;
    }

    public List<Map<String, Object>> queryForList(Connection conn, String sql, Object[] obj) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn
                    .prepareStatement(sql,
                            ResultSet.TYPE_SCROLL_SENSITIVE,
                            ResultSet.CONCUR_READ_ONLY);
            if (obj != null && obj.length > 0) {
                for (int i = 0; i < obj.length; i++) {
                    ps.setObject(i + 1, obj[i]);
                }
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                Map<String, Object> map = new LinkedHashMap<String, Object>();
                for (int k = 1; k <= columnCount; k++) {
                    map.put(rsmd.getColumnLabel(k),
                            rs.getObject(k) == null ? "" : rs.getObject(k));
                }
                list.add(map);
            }
        } finally {
            if (rs != null) rs.close();
            if (rs != null) ps.close();
        }
        return list;
    }

    //根据日期类型获取查询条件
    public String getDateType(String dateType, String column) {
        StringBuffer sbform = new StringBuffer();
        sbform.delete(0, sbform.length());
        switch (dateType) {
            case "01": //今天
                sbform.append(" and ").append(column).append(" between curdate() and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "02": //昨天
                sbform.append(" and ").append(column).append(" between SUBDATE(curdate(),INTERVAL 1 day) and curdate() ");
                break;
            case "03": //最近7天
                sbform.append(" and ").append(column).append(" between SUBDATE(curdate(),INTERVAL 1 week) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "04": //最近30天
                sbform.append(" and ").append(column).append(" between SUBDATE(curdate(),INTERVAL 1 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "05": //最近3个月
                sbform.append(" and ").append(column).append(" between SUBDATE(curdate(),INTERVAL 3 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            case "06": //最近1年
                sbform.append(" and ").append(column).append(" between SUBDATE(curdate(),INTERVAL 1 year) and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
            default: //今天
                sbform.append(" and ").append(column).append(" between curdate() and ADDDATE(curdate(),INTERVAL 1 day) ");
                break;
        }
        return sbform.toString();
    }

    //修改商机跟进中状态
    public PageData updateOpportunityStage(TransactionStatus newg_status, DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("修改商机跟进状态失败");
        try {
            String sb = " update opportunity set opportunitystage='02' where uuid='" + param.get("opportunityId") + "' ";
            this.getJdbcTemplate_newg().update(sb);
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {

        }
        return res;
    }

    public long updateReutrnId(Connection conn, String sql, Object[] obj) throws SQLException {
        PreparedStatement ps = null;
        long autoIncKeyFromApi = -1;
        try {
            ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            if (obj != null && obj.length > 0) {
                for (int i = 0; i < obj.length; i++) {
                    ps.setObject(i + 1, obj[i]);
                }
            }
            ps.executeUpdate();
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                autoIncKeyFromApi = rs.getInt(1);
            }
        } finally {
            try {
                ps.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return autoIncKeyFromApi;
    }

    public PageData getInsertSQL(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        UUID uuid = UUID.randomUUID();
        String tableName = (String) param.get("tableName");
        if (StringUtils.isEmpty((String) param.get("uuid"))) {
            param.put("uuid", uuid.toString());
        }

        StringBuffer sb_field = new StringBuffer();
        StringBuffer sb = new StringBuffer();
        StringBuffer sb_value = new StringBuffer();

        if (req.getUinfo() != null) {
            param.put("inputUser", req.getUinfo().getId());
            param.put("createUid", req.getUinfo().getId());
            param.put("modifyUid", req.getUinfo().getId());
        } else {
            param.put("createUid", "000");
            param.put("modifyUid", "000");
        }

        if (StringUtils.isEmpty((String) param.get("createTime"))) {
            String t = DBUtils.getCurTime();
            param.put("createTime", t);
            param.put("modifyTime", t);
        }


        List<String> listData = new ArrayList<String>();
        sb.delete(0, sb.length());
        sb.append(
                " select column_name column_name,is_nullable,column_type,column_comment,column_default,data_type  from INFORMATION_SCHEMA.Columns where lower(TABLE_SCHEMA)='")
                .append(Globals.DBNAME).append("' and lower(table_name)=? and extra<>'auto_increment' ");
        List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{tableName});
        for (Map<String, Object> data : list) {
            String column_name = data.get("column_name") == null ? (String) data.get("column_name".toUpperCase())
                    : (String) data.get("column_name");
            String is_nullable = data.get("is_nullable") == null ? (String) data.get("is_nullable".toUpperCase())
                    : (String) data.get("is_nullable");
            String column_comment = data.get("column_comment") == null
                    ? (String) data.get("column_comment".toUpperCase())
                    : (String) data.get("column_comment");
            String column_default = data.get("column_default") == null
                    ? (String) data.get("column_default".toUpperCase())
                    : (String) data.get("column_default");
            String column_value = Objects.toString(param.get(column_name), null);
            String data_type = data.get("data_type") == null ? (String) data.get("data_type".toUpperCase())
                    : (String) data.get("data_type");

            if ("NO".equals(is_nullable)) {
                if (StringUtils.isBlank(column_value) && StringUtils.isBlank(column_default)) {
                    res.setRetCode(9);
                    res.setRetMessage(column_comment + "(" + column_name + ")不能为空！");
                    return res;
                }
            }

            if (column_value != null) {
                if (data_type.indexOf("int") >= 0 || data_type.indexOf("decimal") >= 0) {
                    if (!DBUtils.isNumber(column_value)) {
                        res.setRetCode(9);
                        res.setRetMessage(column_comment + "(" + column_name + ")类型错误！");
                        return res;
                    }
                }
            }

            if (!StringUtils.isBlank(column_value)) {
                sb_field.append("`").append(column_name).append("`,");
                sb_value.append("?").append(",");
                listData.add(column_value);
            }
        }
        sb_field.deleteCharAt(sb_field.length() - 1);
        sb_value.deleteCharAt(sb_value.length() - 1);
        sb_field.insert(0, "(").insert(0, "`" + tableName + "`").insert(0, "insert into ");
        sb_field.append(")values(").append(sb_value).append(")");
        Map<String, Object> data = new HashMap<String, Object>();
        data.put("sb_field", sb_field);
        data.put("listData", listData);
        res.setData(data);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getUpdateSQL(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        String tableName = (String) param.get("tableName");
        StringBuffer sb_field = new StringBuffer();
        StringBuffer sb = new StringBuffer();
        StringBuffer sb_where = new StringBuffer();
        List<String> listData = new ArrayList<String>();
        String keys = (String) param.get("keys");
        if (keys == null || keys.equals("")) {
            res.setRetCode(9);
            res.setRetMessage("主键不能为空！");
            return res;
        }
        String[] keylist = keys.split(";");
        for (int i = 0; i < keylist.length; i++) {
            String val = (String) param.get(keylist[i]);
            if (StringUtils.isEmpty(val)) {
                res.setRetCode(9);
                res.setRetMessage(keylist[i] + "不能为空！");
                return res;
            }
        }
        if (req.getUinfo() != null) {
            param.put("modifyUid", req.getUinfo().getId());
        } else {
            param.put("modifyUid", "000");
        }

        String t = DBUtils.getCurTime();
        param.put("modifyTime", t);

        sb_where.append(" where ");

        sb.delete(0, sb.length());
        sb.append(
                " select column_name column_name,is_nullable,column_type,column_comment,column_default,data_type  from INFORMATION_SCHEMA.Columns where lower(TABLE_SCHEMA)='")
                .append(Globals.DBNAME).append("' and lower(table_name)=? and extra<>'auto_increment' ");
        List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{tableName});
        for (Map<String, Object> data : list) {
            String column_name = data.get("column_name") == null ? (String) data.get("column_name".toUpperCase())
                    : (String) data.get("column_name");
            String is_nullable = data.get("is_nullable") == null ? (String) data.get("is_nullable".toUpperCase())
                    : (String) data.get("is_nullable");
            String column_comment = data.get("column_comment") == null
                    ? (String) data.get("column_comment".toUpperCase())
                    : (String) data.get("column_comment");
            String column_default = data.get("column_default") == null
                    ? (String) data.get("column_default".toUpperCase())
                    : (String) data.get("column_default");
            String column_value = Objects.toString(param.get(column_name), null);
            String data_type = data.get("data_type") == null ? (String) data.get("data_type".toUpperCase())
                    : (String) data.get("data_type");

            if ("NO".equals(is_nullable)) {
                if ("".equals(column_value) && StringUtils.isBlank(column_default)) {
                    res.setRetCode(9);
                    res.setRetMessage(column_comment + "不能为空！");
                    return res;
                }
            }

            if (column_value != null) {
                if (data_type.indexOf("int") >= 0 || data_type.indexOf("decimal") >= 0) {
                    if (!DBUtils.isNumber(column_value)) {
                        res.setRetCode(9);
                        res.setRetMessage(column_comment + "(" + column_name + ")类型错误！");
                        return res;
                    }
                }
            }

            if (column_value != null) {
                boolean isPK = false;
                for (String key_name : keylist) {
                    if (column_name.equals(key_name)) {
                        isPK = true;
                        break;
                    }
                }
                if (!isPK) {
                    sb_field.append("`").append(column_name).append("`=?,");
                    listData.add(column_value);
                }
            }
        }
        sb_field.deleteCharAt(sb_field.length() - 1);
        sb_field.insert(0, " set ").insert(0, tableName).insert(0, "update ");

        for (String column_name : keylist) {
            sb_where.append(column_name).append("=? and ");
            String column_value = (String) param.get(column_name);
            listData.add(column_value);
        }
        sb_where.append(" 1=1 ");
        sb_field.append(sb_where);

        Map<String, Object> data = new HashMap<String, Object>();
        data.put("sb_field", sb_field);
        data.put("listData", listData);
        res.setData(data);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    @SuppressWarnings("unchecked")
    public PageData insertTableData(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();

        PageData sqlInfo = getInsertSQL(req);
        if (sqlInfo.getRetCode() != 0) {
            conn.rollback();
            return sqlInfo;
        }
        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.update(conn, sb_field.toString(), listData.toArray());

        //线索添加id把uuid转换成clueid
        String uuid = (String) req.getReqData().get("uuid");
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateClueId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("clueId"))) {
                req.getReqData().put("clueId", uuid);
            } else {
                req.getReqData().put("clueId", req.getReqData().get("clueId"));
            }

        }
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateCustomerId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("customerId"))) {
                req.getReqData().put("customerId", uuid);
            } else {
                req.getReqData().put("customerId", req.getReqData().get("customerId"));
            }

        }
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateOpportunityId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("opportunityId"))) {
                req.getReqData().put("opportunityId", uuid);
            } else {
                req.getReqData().put("opportunityId", req.getReqData().get("opportunityId"));
            }

        }
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateProjectId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("projectId"))) {
                req.getReqData().put("projectId", uuid);
            } else {
                req.getReqData().put("projectId", req.getReqData().get("projectId"));
            }

        }

        // 添加操作日志
        if (!"0".equals(req.getReqData().get("isLog"))) {
            //添加操作日志
            SaveOperatelog(req, (String) req.getReqData().get("OperationLogType"), (String) req.getReqData().get("OperationLogName"), sb_field + "---" + listData, conn);
        }

        req.getReqData().put("uuid", uuid);
        res.setRetCode(0);
        res.setRetMessage("OK");
        res.setKey(uuid);
        return res;
    }

    @SuppressWarnings("unchecked")
    public PageData updateTableData(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        PageData sqlInfo = getUpdateSQL(req);
        if (sqlInfo.getRetCode() != 0) {
            conn.rollback();
            return sqlInfo;
        }
        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.update(conn, sb_field.toString(),
                listData.toArray());

        //线索添加id把uuid转换成clueid
        String uuid = (String) req.getReqData().get("uuid");
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateClueId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("clueId"))) {
                req.getReqData().put("clueId", uuid);
            } else {
                req.getReqData().put("clueId", req.getReqData().get("clueId"));
            }
        }
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateCustomerId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("customerId"))) {
                req.getReqData().put("customerId", uuid);
            } else {
                req.getReqData().put("customerId", req.getReqData().get("customerId"));
            }
        }
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateOpportunityId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("opportunityId"))) {
                req.getReqData().put("opportunityId", uuid);
            } else {
                req.getReqData().put("opportunityId", req.getReqData().get("opportunityId"));
            }
        }
        if ((String) req.getReqData().get("OperationLogId") != null && (String) req.getReqData().get("OperationLogId") == "updateProjectId") {
            if (StringUtils.isEmpty((String) req.getReqData().get("projectId"))) {
                req.getReqData().put("projectId", uuid);
            } else {
                req.getReqData().put("projectId", req.getReqData().get("projectId"));
            }
        }

        SaveOperatelog(req, (String) req.getReqData().get("OperationLogType"), (String) req.getReqData().get("OperationLogName"), sb_field + "---" + listData, conn);

        req.getReqData().put("uuid", uuid);
        res.setRetCode(0);
        res.setRetMessage("OK");
        res.setKey(uuid);
        return res;
    }

    public int queryForInt(Connection conn, String sql, Object[] obj) throws SQLException {
        int cnt = 0;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (obj != null && obj.length > 0) {
                for (int i = 0; i < obj.length; i++) {
                    ps.setObject(i + 1, obj[i]);
                }
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                cnt = rs.getInt(1);
            }
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (ps != null)
                    ps.close();
            } catch (Exception e) {
                // TODO: handle exception
            }
        }
        return cnt;
    }

    /**
     * 获取本月第一天
     * @return String
     **/
    public String getMonthStart() {
        Calendar cal = Calendar.getInstance();
        cal.add(Calendar.MONTH, 0);
        cal.set(Calendar.DAY_OF_MONTH, 1);
        Date time = cal.getTime();
        return new SimpleDateFormat("yyyy-MM-dd").format(time) + " 00:00:00";
    }

    /**
     * 获取本月最后一天
     * @return String
     **/
    public String getMonthEnd() {
        Calendar cal = Calendar.getInstance();
        cal.set(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH));
        Date time = cal.getTime();
        return new SimpleDateFormat("yyyy-MM-dd").format(time) + " 23:59:59";
    }

    /**
     * 获取本周的第一天
     * @return String
     **/
    public String getWeekStart() {
        Calendar cal = Calendar.getInstance();
        cal.add(Calendar.WEEK_OF_MONTH, 0);
        cal.set(Calendar.DAY_OF_WEEK, 2);
        Date time = cal.getTime();
        return new SimpleDateFormat("yyyy-MM-dd").format(time) + " 00:00:00";
    }

    /**
     * 获取本周的最后一天
     * @return String
     **/
    public String getWeekEnd() {
        Calendar cal = Calendar.getInstance();
        cal.set(Calendar.DAY_OF_WEEK, cal.getActualMaximum(Calendar.DAY_OF_WEEK));
        cal.add(Calendar.DAY_OF_WEEK, 1);
        Date time = cal.getTime();
        return new SimpleDateFormat("yyyy-MM-dd").format(time) + " 23:59:59";
    }


    /**
     * get Calendar of given year
     * @param year
     * @return
     */
    private Calendar getCalendarFormYear(int year){
        Calendar cal = Calendar.getInstance();
        cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
        cal.set(Calendar.YEAR, year);
        return cal;
    }

    /**
     * 获取某一年的某一周开始日期
     * @param year
     * @param weekNo
     * @return
     */
    public String getStartDayOfWeekNo(int year,int weekNo){
        Calendar cal = getCalendarFormYear(year);
        cal.set(Calendar.WEEK_OF_YEAR, weekNo);
        String s = cal.get(Calendar.YEAR) + "-" + ((cal.get(Calendar.MONTH) + 1)>9?(cal.get(Calendar.MONTH) + 1):"0"+(cal.get(Calendar.MONTH) + 1)) + "-" + (cal.get(Calendar.DAY_OF_MONTH)>9?cal.get(Calendar.DAY_OF_MONTH):"0"+cal.get(Calendar.DAY_OF_MONTH));
        return s;

    }

    /**
     * 获取某一年的某一周结束日期
     * @param year
     * @param weekNo
     * @return
     */
    public String getEndDayOfWeekNo(int year,int weekNo){
        Calendar cal = getCalendarFormYear(year);
        cal.set(Calendar.WEEK_OF_YEAR, weekNo);
        cal.add(Calendar.DAY_OF_WEEK, 6);
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String s = cal.get(Calendar.YEAR) + "-" + ((cal.get(Calendar.MONTH) + 1)>9?(cal.get(Calendar.MONTH) + 1):"0"+(cal.get(Calendar.MONTH) + 1)) + "-" + (cal.get(Calendar.DAY_OF_MONTH)>9?cal.get(Calendar.DAY_OF_MONTH):"0"+cal.get(Calendar.DAY_OF_MONTH));
        return s;
    }

}
